----Insert Trigger----
CREATE TRIGGER checkRegularPatientInsert
ON Prescribes FOR INSERT AS

BEGIN
	DECLARE @doctorIdNew  INT
	DECLARE @patientIdNew INT	

	SET @patientIdNew = (SELECT patientId FROM INSERTED)
	SET @doctorIdNew = (SELECT doctorId FROM INSERTED)

	IF(EXISTS(
			SELECT p.doctorId, p.patientId
			FROM Prescribes p
			WHERE	p.doctorId = @doctorIdNew
					AND p.patientId = @patientIdNew
			GROUP BY p.doctorId, p.patientId
		    HAVING COUNT(DISTINCT(p.date)) = 5))
		    
	BEGIN
		INSERT INTO RegularPatient VALUES (@patientIdNew, @doctorIdNew);
	END
END

----Update Trigger----
CREATE TRIGGER checkRegularPatientUpdate
ON Prescribes FOR UPDATE AS

BEGIN
	DECLARE @patientIdOld INT
	DECLARE @doctorIdNew  INT
	DECLARE @patientIdNew INT
	DECLARE @doctorIdOld  INT
	DECLARE @updatedCount INT

    SET @updatedCount = (SELECT COUNT(*) FROM INSERTED)

    IF (@updatedCount = 1)
    BEGIN
	    SET @patientIdOld = (SELECT patientId FROM DELETED)
	    SET @doctorIdOld = (SELECT doctorId FROM DELETED)
	    SET @patientIdNew = (SELECT patientId FROM INSERTED)
	    SET @doctorIdNew = (SELECT doctorId FROM INSERTED)

	    IF(EXISTS(
			SELECT p.doctorId, p.patientId
			FROM Prescribes p, RegularPatient rp
			WHERE	p.doctorId = rp.doctorId
					AND p.patientId = rp.patientId
					AND p.doctorId = @doctorIdOld
					AND p.patientId = @patientIdOld
			GROUP BY p.doctorId, p.patientId
		    HAVING COUNT(DISTINCT(p.date)) = 4))
		    
	    BEGIN
		    DELETE	FROM RegularPatient
		    WHERE	doctorId = @doctorIdOld
				AND patientId = @patientIdOld;
	    END

	    IF(EXISTS(
			SELECT doctorId, patientId
			FROM Prescribes
			    WHERE	doctorId = @doctorIdNew
					AND patientId = @patientIdNew
			    GROUP BY doctorId, patientId
		        HAVING COUNT(DISTINCT(date)) = 5)
	        AND NOT EXISTS(
			    SELECT *
			    FROM RegularPatient
		        WHERE	doctorId = @doctorIdNew
					AND patientID = @patientIdNew))
		       
	    BEGIN
		    INSERT INTO RegularPatient VALUES (@patientIdNew, @doctorIdNew);
	    END
    END
END

----Delete Trigger----
CREATE TRIGGER checkRegularPatientDelete
ON Prescribes FOR DELETE AS

BEGIN
	DECLARE @patientIdOld INT
	DECLARE @doctorIdOld  INT

	SET @patientIdOld = (SELECT patientId FROM DELETED)
	SET @doctorIdOld = (SELECT doctorId FROM DELETED)

	IF(EXISTS(
			SELECT p.doctorId, p.patientId
			FROM Prescribes p
			WHERE	p.doctorId = @doctorIdOld
					AND p.patientId = @patientIdOld
			GROUP BY p.doctorId, p.patientId
		    HAVING COUNT(DISTINCT(p.date)) = 4))
		    
	BEGIN
		 DELETE FROM RegularPatient
		 WHERE	doctorId = @doctorIdOld
				AND patientId = @patientIdOld
	END
END


